package com.sport.facilities.booking.db;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.sport.facilities.booking.system.model.Booking;
import com.sport.facilities.booking.system.model.Friend;

public class DatabaseHelper extends SQLiteOpenHelper {

	// All Static variables
	// Database Version
	private static final int DATABASE_VERSION = 12;

	// Database Name
	private static final String DATABASE_NAME = "PolyuSportsFacilities";

	// Activities table name
	private static final String TABLE_ACTIVITIES = "Activities";
	private static final String TABLE_FRIENDLIST = "FriendList";

	// Activities Table Columns names
	private static final String KEY_ID = "id";
	private static final String KEY_ACTIVITY = "activity";
	private static final String KEY_BOOKINGTYPE = "bookingtype";
	private static final String KEY_CENTRE = "centre";
	private static final String KEY_COURT = "court";
	private static final String KEY_DATE = "date";
	private static final String KEY_TIME = "time";
	private static final String KEY_ALARMID = "alarmid";
	
	

	private static final String KEY_FRIENDNAME = "friendname";
	private static final String KEY_FRIENDEMAIL = "friendemail";
	private static final String KEY_FRIENDPHONE = "friendphone";
	
	//private static final String KEY_DATETIME = "datetime";

	public DatabaseHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	// Creating Tables
	@Override
	public void onCreate(SQLiteDatabase db) {
		String CREATE_ACTIVITIES_TABLE = "CREATE TABLE " + TABLE_ACTIVITIES
				+ "(" + KEY_ID + " INTEGER PRIMARY KEY," + KEY_ACTIVITY
				+ " TEXT," + KEY_BOOKINGTYPE + " TEXT," +  KEY_CENTRE + " TEXT," + KEY_COURT + " TEXT,"
				+ KEY_DATE + " TEXT," + KEY_TIME + " TEXT," + KEY_ALARMID + " INT"
				+ ")";
		db.execSQL(CREATE_ACTIVITIES_TABLE);
		
		String CREATE_FRIENDLIST_TABLE = "CREATE TABLE " + TABLE_FRIENDLIST
				+ "(" + KEY_ID + " INTEGER PRIMARY KEY," + KEY_FRIENDNAME
				+ " TEXT," + KEY_FRIENDEMAIL + " TEXT," +  KEY_FRIENDPHONE + " TEXT"
				+ ")";
		db.execSQL(CREATE_FRIENDLIST_TABLE);
		
	}

	// Upgrading database
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// Drop older table if existed
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_ACTIVITIES);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_FRIENDLIST);

		// Create tables again
		onCreate(db);
	}

	/**
	 * All CRUD(Create, Read, Update, Delete) Operations
	 * @return 
	 */

	

	public // Adding new Friend
	long addFriend(Friend friend) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(KEY_FRIENDNAME, friend.getName());
		values.put(KEY_FRIENDEMAIL, friend.getEmail());
		values.put(KEY_FRIENDPHONE, friend.getPhone());

		// Inserting Row
		long success = db.insert(TABLE_FRIENDLIST, null, values);
		db.close(); // Closing database connection
		
		return success;
	}

	
	public // Adding new Activities
	void addActivity(Booking booking) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(KEY_ACTIVITY, booking.getActivity());
		values.put(KEY_BOOKINGTYPE, booking.getBkType());
		values.put(KEY_CENTRE, booking.getCentre());
		values.put(KEY_COURT, booking.getCourt());
		values.put(KEY_DATE, booking.getDate());
		values.put(KEY_TIME, booking.getTime());
		values.put(KEY_ALARMID, booking.getalarmId());
		//values.put(KEY_DATETIME, booking.getDate() + booking.getTime());

		// Inserting Row
		db.insert(TABLE_ACTIVITIES, null, values);
		db.close(); // Closing database connection
	}


	// Getting single Friend
	Friend getFriend(int id) {
		SQLiteDatabase db = this.getReadableDatabase();

		Cursor cursor = db.query(TABLE_FRIENDLIST, new String[] { KEY_ID,
				KEY_FRIENDNAME, KEY_FRIENDEMAIL, KEY_FRIENDPHONE}, KEY_ID + "=?",
				new String[] { String.valueOf(id) }, null, null, null, null);
		if (cursor != null)
			cursor.moveToFirst();

		Friend friend = new Friend(cursor.getInt(0),
				cursor.getString(1), cursor.getString(2), cursor.getString(3));
		// return booking
		return friend;
	}
	
	// Getting single booking
	Booking getActivity(int id) {
		SQLiteDatabase db = this.getReadableDatabase();

		Cursor cursor = db.query(TABLE_ACTIVITIES, new String[] { KEY_ID,
				KEY_ACTIVITY, KEY_BOOKINGTYPE, KEY_CENTRE, KEY_COURT, KEY_DATE, KEY_TIME , KEY_ALARMID}, KEY_ID + "=?",
				new String[] { String.valueOf(id) }, null, null, null, null);
		if (cursor != null)
			cursor.moveToFirst();

		Booking booking = new Booking(cursor.getString(0),
				cursor.getString(1), cursor.getString(2), cursor.getString(3),
				cursor.getString(4), cursor.getString(5), cursor.getString(6), cursor.getInt(7));
		// return booking
		return booking;
	}

	public Booking getActivitybyDateTime(String Date, String Time) {
		SQLiteDatabase db = this.getReadableDatabase();

		Cursor cursor = db.query(TABLE_ACTIVITIES, new String[] { KEY_ID,
				KEY_ACTIVITY, KEY_BOOKINGTYPE, KEY_CENTRE, KEY_COURT, KEY_DATE, KEY_TIME , KEY_ALARMID}, KEY_DATE + "=? and " + KEY_TIME + "=?",
				new String[] { String.valueOf(Date),String.valueOf(Time) }, null, null, null, null);
		if (cursor != null)
			cursor.moveToFirst();

		Booking booking = new Booking(cursor.getString(0),
				cursor.getString(1), cursor.getString(2), cursor.getString(3),
				cursor.getString(4), cursor.getString(5), cursor.getString(6), cursor.getInt(7));
		// return booking
		return booking;
	}
	// Getting All Booking
	public List<Booking> getAllBooking() {
		List<Booking> bookingList = new ArrayList<Booking>();
		// Select All Query
		String selectQuery = "SELECT  * FROM " + TABLE_ACTIVITIES;

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				Booking booking = new Booking();
				booking.setId(cursor.getString(0));				
				booking.setActivity(cursor.getString(1));
				booking.setBkType(cursor.getString(2));
				booking.setCentre(cursor.getString(3));
				booking.setCourt(cursor.getString(4));
				booking.setDate(cursor.getString(5));
				booking.setTime(cursor.getString(6));
				booking.setalarmId(cursor.getInt(7));
				//booking.setDateTime(cursor.getString(6));
				// Adding activity to list
				bookingList.add(booking);
			} while (cursor.moveToNext());
		}

		// return activity list
		return bookingList;
	}

	

	// Getting All Friend
	public List<Friend> getAllFriend() {
		List<Friend> friendList = new ArrayList<Friend>();
		// Select All Query
		String selectQuery = "SELECT  * FROM " + TABLE_FRIENDLIST +" ORDER BY " + KEY_FRIENDNAME + " DESC";

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				Friend friend = new Friend();
				friend.setId(cursor.getInt(0));				
				friend.setName(cursor.getString(1));
				friend.setEmail(cursor.getString(2));
				friend.setPhone(cursor.getString(3));
				friendList.add(friend);
			} while (cursor.moveToNext());
		}

		// return friend list
		return friendList;
	}	
	

	// Updating single friend
	public int updateFriend(Friend friend) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(KEY_FRIENDNAME, friend.getName());
		values.put(KEY_FRIENDEMAIL, friend.getEmail());
		values.put(KEY_FRIENDPHONE, friend.getPhone());

		// updating row
		return db.update(TABLE_FRIENDLIST, values, KEY_ID + " = ?",
				new String[] { String.valueOf(friend.getId()) });
	}

	
	// Updating single activity
	public int updateActivity(Booking booking) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(KEY_ACTIVITY, booking.getActivity());
		values.put(KEY_BOOKINGTYPE, booking.getBkType());
		values.put(KEY_CENTRE, booking.getCentre());
		values.put(KEY_COURT, booking.getCourt());
		values.put(KEY_DATE, booking.getDate());
		values.put(KEY_TIME, booking.getTime());
		values.put(KEY_ALARMID, booking.getalarmId());
		//values.put(KEY_DATETIME, booking.getDateTime());

		// updating row
		return db.update(TABLE_ACTIVITIES, values, KEY_ID + " = ?",
				new String[] { String.valueOf(booking.getId()) });
	}

	// Deleting single Friend
	public int deleteFriend(Integer Id) {
		SQLiteDatabase db = this.getWritableDatabase();
		return db.delete(TABLE_FRIENDLIST, KEY_ID + " = ?",
				new String[] { String.valueOf(Id) });
		//db.close();
	}

	// Getting contacts Count
	/*
	public int getContactsCount() {
		String countQuery = "SELECT  * FROM " + TABLE_ACTIVITIES;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(countQuery, null);
		cursor.close();

		// return count
		return cursor.getCount();
	}
*/
	
}
